<?php

declare(strict_types=1);

namespace app\model;

use app\model\base\BaseJtkGoodsShopModel;
use think\facade\Db;

/**
 * 
 *
 * @mixin \think\Model
 * @property int $id
 * @property int $jtk_goods_id 聚推客产品id 即商品id
 * @property int $location_id 站点id
 * @property string $name 商家名称
 * @property string $latitude 纬度
 * @property string $longitude 经度
 */
class JtkGoodsShop extends BaseJtkGoodsShopModel
{
    /**
     * 商品门店距离query 子查询语句
     * @param $locationId
     * @param $latitude
     * @param $longitude
     * @return string
     */
    public static function getDistanceQuery($locationId, $latitude, $longitude) {
        $distanceRaw = "ROUND(6378.138*2*ASIN(SQRT(POW(SIN(({$latitude}*PI()/180-latitude*PI()/180)/2),2)+COS({$latitude}*PI()/180)*COS(latitude*PI()/180)*POW(SIN(({$longitude}*PI()/180-longitude*PI()/180)/2),2)))*1000) AS distance";

        $subQuery = self::query()
            ->field('*')
            ->fieldRaw($distanceRaw)
            ->where('location_id', $locationId)
//            ->where('jtk_goods_id', '941425')
            ->order('distance asc')
            ->buildSql();

        $shopQuery = Db::table($subQuery . ' shopDis')
            ->field("shopDis.jtk_goods_id, min(shopDis.distance) as min_dis, latitude, longitude")
            ->group('shopDis.jtk_goods_id')
            ->order('min_dis asc')
            ->buildSql();

        return $shopQuery;

    }
}
